﻿using System;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for CustomersDAL
/// </summary>
public class CustomersDAL
{
    dbConnection db = new dbConnection();
	public CustomersDAL()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public  int CreateCustomers(CustomersBO Customer)
    {
        SqlConnection cn = null;
        SqlCommand cmd = null;
        int Counter = 0;

        dbConnection db = new dbConnection();
        
        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();

            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO [Customers] ( CustomerName, AccountId,AddressId,ImageName,ImagePath) values (@CustomerName,@AccountId,@AddressId,@ImageName,@ImagePath)";
            cmd.Parameters.AddWithValue("@CustomerName", Customer.CustomerName);
            cmd.Parameters.AddWithValue("@AccountId", Customer.AccountId);
            cmd.Parameters.AddWithValue("@AddressId", Customer.AddressId);
            cmd.Parameters.AddWithValue("@ImageName", Customer.ImageName);
            cmd.Parameters.AddWithValue("@ImagePath", Customer.ImagePath);

            cn.Open();
            Counter = cmd.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            if (cn != null)
            {
                cn.Close();
                cn.Dispose();
                cn = null;
            }
        }
        return (Counter);
    }
    public  DataTable AddressFillDropDown()
    {
        SqlConnection cn = null;
        SqlCommand cmd = null;
        DataSet dSet1 = null;
        SqlDataAdapter sda = null;

        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();
            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select AddressId,Addresses.AddressLine1+','+Addresses.AddressLine2 + ',' +Addresses.AddressLine3+','+Addresses.City+','+CAST( PhoneNumber AS nvarchar(50)) as Address from [Addresses]";
            dSet1 = new DataSet();
            sda = new SqlDataAdapter(cmd);

            sda.Fill(dSet1, "Addresses");

            return dSet1.Tables["Addresses"];

        }

        catch
        {

            throw;

        }

        finally
        {

            dSet1.Dispose();

            sda.Dispose();

            cn.Close();

            cn.Dispose();

        }
    }
    public  DataTable AccountFillDropDown()
    {
        SqlConnection cn = null;
        SqlCommand cmd = null;
        DataSet dSet1 = null;
        SqlDataAdapter sda = null;

        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();
            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select AccountId from [Accounts]";
            dSet1 = new DataSet();
            sda = new SqlDataAdapter(cmd);

            sda.Fill(dSet1, "Accounts");

            return dSet1.Tables["Accounts"];

        }

        catch
        {

            throw;

        }

        finally
        {

            dSet1.Dispose();

            sda.Dispose();

            cn.Close();

            cn.Dispose();

        }
    }

    public  int UpdateCustomer(CustomersBO Customer)
    {
        SqlConnection cn = null;
        SqlCommand cmd = null;
        int Counter = 0;

       

        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();

            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "UPDATE [Customers] SET  [CustomerName]=@CustomerName,[AddressId]=@AddressId,[ImageName]=@ImageName,[ImagePath]=@ImagePath WHERE CustomerId=@CustomerId";

            cmd.Parameters.AddWithValue("@CustomerId", Customer.CustomerId);
            cmd.Parameters.AddWithValue("@CustomerName", Customer.CustomerName);
            //cmd.Parameters.AddWithValue("@AccountId", Customer.AccountId);
            cmd.Parameters.AddWithValue("@AddressId", Customer.AddressId);
            cmd.Parameters.AddWithValue("@ImageName", Customer.ImageName);
            cmd.Parameters.AddWithValue("@ImagePath", Customer.ImagePath);
            cn.Open();
            Counter = cmd.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            if (cn != null)
            {
                cn.Close();
                cn.Dispose();
                cn = null;
            }
        }
        return (Counter);
    }

    public  int DeleteCustomer(CustomersBO Customer)
    {
        SqlConnection cn = null;
        SqlCommand cmd = null;
        int Counter = 0;


        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();

            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "DELETE FROM [Customers] WHERE CustomerId=@CustomerId";

            cmd.Parameters.AddWithValue("@CustomerId", Customer.CustomerId);

            cn.Open();
            Counter = cmd.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            if (cn != null)
            {
                cn.Close();
                cn.Dispose();
                cn = null;
            }
        }
        return (Counter);
    }

	public int DeleteAccount(CustomersBO Customer)
		{
		SqlConnection cn = null;
		SqlCommand cmd = null;
		int Counter = 0;


		try
			{
			cn = new SqlConnection();
			cn.ConnectionString = db.GetCS();

			cmd = new SqlCommand();
			cmd.Connection = cn;
			cmd.CommandType = CommandType.Text;
			cmd.CommandText = "DELETE FROM [ACCOUNTS] WHERE AccountId=@AccountId";

			cmd.Parameters.AddWithValue("@CustomerId", Customer.AccountId);

			cn.Open();
			Counter = cmd.ExecuteNonQuery();

			}
		catch (Exception ex)
			{
			throw new Exception(ex.Message);
			}
		finally
			{
			if (cn != null)
				{
				cn.Close();
				cn.Dispose();
				cn = null;
				}
			}
		return (Counter);
		}


    public  DataTable Load()
    {

        SqlConnection cn = null;
        SqlCommand cmd = null;
        DataSet dSet = null;
        SqlDataAdapter sda = null;

        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();
            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT Customers.CustomerId,Customers.CustomerName,Customers.AccountId,Customers.ImagePath,Addresses.AddressLine1+','+Addresses.AddressLine2 + ',' +Addresses.AddressLine3+','+Addresses.City+','+CAST( PhoneNumber AS nvarchar(50)) as Address FROM Customers,Addresses  where Customers.AddressId=Addresses.AddressId";
            
            dSet = new DataSet();
            sda = new SqlDataAdapter(cmd);

            sda.Fill(dSet, "Customers");

            return dSet.Tables["Customers"];

        }

        catch
        {

            throw;

        }

        finally
        {

            dSet.Dispose();

            sda.Dispose();

            cn.Close();

            cn.Dispose();

        }
    }
    //public virtual int ExecuteInsert(CustomersBO Customer)
    //{
    //    string name = "";
    //    int size = 0;
    //    string path = "";
        
    //    SqlConnection conn = new SqlConnection(db.GetCS());
    //    int Counter = 0;
    //    string sql = "INSERT INTO ImagePath (ImageName, ImageSize, ImagePath) VALUES "

    //                + " (@ImgName,@ImgSize,@ImgPath)";

    //    try
    //    {



    //        conn.Open();

    //        SqlCommand cmd = new SqlCommand(sql, conn);

    //        SqlParameter[] param = new SqlParameter[3];



    //        param[0] = new SqlParameter("@ImgName", SqlDbType.NVarChar, 50);

    //        param[1] = new SqlParameter("@ImgSize", SqlDbType.BigInt, 9999);

    //        param[2] = new SqlParameter("@ImgPath", SqlDbType.VarChar, 50);

           

    //        param[0].Value = name;

    //        param[1].Value = size;

    //        param[2].Value = path;



    //        for (int i = 0; i < param.Length; i++)
    //        {

    //            cmd.Parameters.Add(param[i]);

    //        }



    //        cmd.CommandType = CommandType.Text;

    //        cmd.ExecuteNonQuery();

    //    }

    //    catch (System.Data.SqlClient.SqlException ex)
    //    {

    //        string msg = "Insert Error:";

    //        msg += ex.Message;

    //        throw new Exception(msg);

    //    }

    //    finally
    //    {

    //        conn.Close();

    //    }
    //    return (Counter);

    //}

    public  int CreateImage(CustomersBO Customer)
    {
        SqlConnection cn = null;
        SqlCommand cmd = null;
        int Counter = 0;

        dbConnection db = new dbConnection();

        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();

            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO ImagePath (ImageName, ImageSize, ImagePath) VALUES  (@ImageName,@ImageSize,@ImagePath)";


            cmd.Parameters.AddWithValue("@ImageName", Customer.ImageName);
            cmd.Parameters.AddWithValue("@ImageSize", Customer.ImageSize);
            cmd.Parameters.AddWithValue("@ImagePath", Customer.ImagePath);


            cn.Open();
            Counter = cmd.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            if (cn != null)
            {
                cn.Close();
                cn.Dispose();
                cn = null;
            }
        }
        return (Counter);



    }
    public  string GetImagePath(CustomersBO Customer)
    {
        SqlConnection cn = null;
        SqlCommand cmd = null;
        string ff="";



        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();

            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select ImagePath from Customers where CustomerId=@CustomerId";
            cmd.Parameters.AddWithValue("@CustomerId", Customer.CustomerId);
            cn.Open();
                ff = (String)cmd.ExecuteScalar();
        }
            
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        finally
        {
        if (cn != null)
            {
                cn.Close();
                cn.Dispose();
                cn = null;
            }
        }
            return ff;
        }
    }
    
